#!/usr/bin/env python

import cx_Oracle
import codecs
import string
import getpass
import sys
import time

dtnow_iso = lambda : time.strftime ("%Y-%m-%dT%H%M%S", time.localtime())
#2008-07-16T19:44:18

def list_columns(conStr={'usr':'', 'pwd':'', 'dsn':''}):
    """ """
    con = cx_Oracle.connect(conStr['usr'], conStr['pwd'], conStr['dsn'])
    cursor = con.cursor()
    
    cursor.execute(""" select * 
                         from ALL_TAB_COLUMNS 
                        order by OWNER, TABLE_NAME, COLUMN_ID""")
    result = cursor.fetchall()[:]
    
    o = open('%s_db_out_%s'  % ('oracle_list_columns', dtnow_iso()), 'w')
    #o_err = open('%s_db_err' % (odbc_conn_name), 'w')
    o.write('SCHEMA\tTABLE\tCOLUMN\tcol_data_type\tcol_data_length\tcol_char_decl_length\n'.upper())

    for r in result:
        schema = r[0]
        table = r[1]
        column = r[2]
        c_data_type = r[3]
        c_data_length = r[6]
        c_char_decl_length = r[22]
        
        o.write("%s\t%s\t%s\t%s\t%s\t%s\n" % (schema
                , table
                , column
                , c_data_type
                , c_data_length
                , c_char_decl_length))
    
    o.close()

if 0:
    #import cx_Oracle
    #import pepe.p_sql.oracle_sqlplus as osp
    #import getpass

    ip = '127.0.0.1'    # tunneled to remote server or local connection
    #port = 1522         # shifted to tunneled 1522, on 1521 is local XE instance
    port = 1521
    sid = 'dtrslab3'
    dsn_tns = cx_Oracle.makedsn(ip, port, sid)
    #usr = 'vpresovsky'
    usr = raw_input("Input username: ")
    #pwd = '...'
    pwd = getpass.getpass()
    conString = {'usr': usr, 'pwd': pwd, 'dsn': dsn_tns}

    list_columns(conString)